﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;



using ES_Project.Model;
using MySql.Data.MySqlClient;
using System.Data;
using System.Web.Configuration;
namespace ES_Project.Pages
{
    public partial class WebForm443 : System.Web.UI.Page
    {
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand();
        #region "Page Load"
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                
                empClsMastId.Text = Request.QueryString["empClsMastId"];
                if (!empClsMastId.Text.Equals(""))
                {
                    
                    empClsMastId.Text = Request.QueryString["empClsMastId"];
                    CMD_INSERT.Enabled = false;
                    CMD_EDIT.Enabled = true;
                    Editing();
                }
                else
                {
                    CMD_INSERT.Enabled = true;
                    CMD_EDIT.Enabled = false;
                    BindData();
                }
            }
            
        }
        #endregion

        #region "Editing()"
        void Editing()
        {
            CMD_INSERT.Enabled = false;
            CMD_EDIT.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;

            


            strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, "+
                     "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, "+
                     "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name , ecm.pay_type, ecm.emp_cls_sal,ecm.emp_cls_sal_month,ecm.emp_cls_sal_day,ecm.emp_cls_sal_hours "+
                     "FROM emp_cls_mast ecm "+
                     "LEFT OUTER JOIN emp_mast em "+
                        "ON ecm.emp_id = em.emp_id "+
                     "LEFT OUTER JOIN cls_register_detail crd "+
                        "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id "+
                     "LEFT OUTER JOIN cls_register_mast crm "+
                        "ON crd.cls_reg_id = crm.cls_reg_id " +
                     "where ecm.emp_cls_mast_id = '" + empClsMastId.Text + "'";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                empId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["emp_id"]).ToString();
                empCode.Text = (string)ds4.Tables[0].Rows[i]["emp_code"].ToString();
                empName.Text = (string)ds4.Tables[0].Rows[i]["emp_name"].ToString();
                empClsDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_day"].ToString();
                empClsStart.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_start"].ToString();
                empClsEnd.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_end"].ToString();
                empClsTotalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_total_hours"].ToString();
                clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                clsRegDetailId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                empClsMastId.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_mast_id"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                empClsHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_hours"].ToString();
                //empClsSal.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal"].ToString();
                //empClsSalMonth.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString()));
                //empClsSalMonth.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString();
                //empClsSalDay.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString()));
                //empClsSalDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString();
                //empClsSalHours.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString())); 
                //radio.SelectedValue = (string)ds4.Tables[0].Rows[i]["pay_type"].ToString();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[0]["cls_reg_detail_id"].ToString();

                string strSQL2;
                strSQL2 = "SELECT * FROM  cls_register_detail crd  " +
                     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                     "where crm.cls_reg_code = '" + clsRegCode.Text + "  '";



                MySqlConnection Conn1 = new MySqlConnection(StrConn);
                Conn1.Open();
                MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn1);
                DataSet ds2 = new DataSet();

                da2.Fill(ds2);
                //*** DropDownList ***//
                this.myDDL1.DataSource = ds2;
                this.myDDL1.DataTextField = "cls_course_no";
                this.myDDL1.DataValueField = "cls_reg_detail_id";
                this.myDDL1.DataBind();
                this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                da2 = null;
                Conn1.Close();
                Conn1 = null;
            }
        }
        #endregion


        #region "CMD_EmpCode_Click"
        protected void CMD_EmpCode_Click(object sender, EventArgs e) 
         {


             MySqlConnection Conn = new MySqlConnection(StrConn);
             String strSQL ;


             strSQL = "SELECT * FROM  emp_mast em  " +
                      "where em.emp_code = '" + empCode.Text + "  '";
             //strSQL = "SELECT * FROM emp_cls_mast ecm left outer join cls_register_detail crd  "+
             //            "on ecm.cls_reg_detail_id = crd.cls_reg_detail_id  "+
             //            "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
             //            "where crm.cls_reg_code = '"+clsRegCode.Text+"'";



             Conn.Open();
             MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
             DataSet ds4 = new DataSet();
             da4.Fill(ds4);
             //dtAdapter.Fill(dt);
             da4 = null;
             Conn.Close();
             Conn = null;
             //dtAdapter = null;
             //objConn.Close();
             //objConn = null;


             int _row = ds4.Tables[0].Rows.Count;
             int i = 0;
             if (_row > i)
             {


                 empName.Text = (string)ds4.Tables[0].Rows[0]["emp_name"].ToString();
                 empId.Text = (string)ds4.Tables[0].Rows[0]["emp_id"].ToString();


             }
         }
        #endregion

        #region "CMD_CourseCode_Click"
        protected void CMD_CourseCode_Click(object sender, EventArgs e)
        {

            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


           strSQL = "SELECT * FROM  cls_register_detail crd  "+
                     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
                     "where crm.cls_reg_code = '"+ clsRegCode.Text+"  '";
            //strSQL = "SELECT * FROM emp_cls_mast ecm left outer join cls_register_detail crd  "+
            //            "on ecm.cls_reg_detail_id = crd.cls_reg_detail_id  "+
            //            "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
            //            "where crm.cls_reg_code = '"+clsRegCode.Text+"'";



            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            //dtAdapter.Fill(dt);
            da4 = null;
            Conn.Close();
            Conn = null;
            //dtAdapter = null;
            //objConn.Close();
            //objConn = null;
            

            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                
                clsName.Text = (string)ds4.Tables[0].Rows[0]["cls_name"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[0]["cls_course_time"].ToString();
                

            }
            //*** DropDownList ***//
            this.myDDL1.DataSource = ds4;
            this.myDDL1.DataTextField = "cls_course_no";
            this.myDDL1.DataValueField = "cls_reg_detail_id";
            this.myDDL1.DataBind();	

            //int _row = ds4.Tables[0].Rows.Count;
            //int i = 0;
            //if (_row > i)
            //{

            //    clsRegDetailId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["cls_reg_detail_id"]).ToString();
            //    clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
            //    clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
            //    clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
            //    clsCourseAmt.Text = (string)ds4.Tables[0].Rows[i]["cls_course_amt"].ToString();
            //    clsRegId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_id"].ToString();

            //}

        }

        #endregion

        #region "CMD_INSERT_Click"
        protected void CMD_INSERT_Click(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT":

                        emp_cls_mast ecm = new emp_cls_mast();
                        ecm.emp_id = Convert.ToInt32(empId.Text);
                        ecm.emp_cls_day = empClsDay.Text;
                        ecm.emp_cls_total_hours = empClsTotalHours.Text;
                        ecm.emp_cls_hours = empClsHours.Text;
                        ecm.emp_cls_start = empClsStart.Text;
                        ecm.emp_cls_end = empClsEnd.Text;
                        ecm.create_by = "Tong";
                        ecm.create_date = DateTime.Now;
                        ecm.start_date = Convert.ToDateTime(startDate.Text);
                       
                        ecm.cls_reg_detail_id = Convert.ToInt32(myDDL1.SelectedValue);
                        context.emp_cls_mast.AddObject(ecm);
                       
                        if (context.SaveChanges() > 0)
                        {
                            
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('บันทึกเรียบร้อยแล้ว')", true);
                            BindData();
                            //GridView4.DataBind();
                            //GridView4.SelectedIndex = -1;
                            int i = 0;
                            string a = "";
                            for (i = 0; i <= Convert.ToInt32(clsCourseTime.Text) - 1; i++)
                            {
                                emp_schedule es = new emp_schedule();

                                if (i == 0)
                                {
                                    es.emp_dateschedule = Convert.ToDateTime(startDate.Text);
                                    a = startDate.Text;
                                }
                                else
                                {
                                    a = Convert.ToDateTime(a).AddDays(7).ToString();
                                    es.emp_dateschedule = Convert.ToDateTime(a);

                                }
                                es.emp_cls_mast_id = ecm.emp_cls_mast_id.ToString();
                                
                                es.emp_id = empId.Text;
                                es.create_by = "Tong";
                                es.create_date = DateTime.Now;

                                context.emp_schedule.AddObject(es);
                            }


                            // context.SaveChanges();
                            if (context.SaveChanges() > 0)
                            {
                                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('บันทึกเรียบร้อยแล้ว')", true);
                               


                            }
                            empCode.Text = "";
                            empName.Text = "";
                            empClsDay.Text = "";
                            empId.Text = "";
                            empClsEnd.Text = "";
                            empClsStart.Text = "";
                            empClsTotalHours.Text = "";
                            empClsHours.Text = "";
                            clsRegDetailId.Text = "";
                            clsRegCode.Text = "";
                            clsName.Text = "";
                            clsCourseTime.Text = "";
                            //empClsSal.Text = "";
                            //empClsSalDay.Text = "";
                            //empClsSalMonth.Text = "";
                            //empClsSalHours.Text = "";
                            myDDL1.Items.Clear();
                            //this.myDDL1.Items.Add("");
                            //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));
                            
                        }

                        break;
                    case "CMD_EDIT":
                       
                        List<emp_cls_mast> crm2 = context.emp_cls_mast.Where("it.[emp_cls_mast_id]=" + empClsMastId.Text + " ").ToList();
                        if (crm2.Count() == 1)
                        {
                            emp_cls_mast crmEdit = context.emp_cls_mast.Where("it.[emp_cls_mast_id]=" + empClsMastId.Text + " ").First();
                            if (crmEdit != null)
                            {
                                crmEdit.emp_id = Convert.ToInt32(empId.Text);
                                crmEdit.emp_cls_day = empClsDay.Text;
                                crmEdit.emp_cls_total_hours = empClsTotalHours.Text;
                                crmEdit.emp_cls_start = empClsStart.Text;
                                crmEdit.emp_cls_end = empClsEnd.Text;
                                crmEdit.emp_cls_hours = empClsHours.Text;
                                //crmEdit.pay_type = radio.SelectedValue;
                                 //ecm.cls_reg_detail_id = Convert.ToInt32(clsRegDetailId.Text);
                                crmEdit.cls_reg_detail_id = Convert.ToInt32(myDDL1.SelectedValue);
                                crmEdit.update_by = "Tong";
                                crmEdit.update_date = DateTime.Now;
                                crmEdit.start_date = Convert.ToDateTime(startDate.Text);
                                

                                if (context.SaveChanges() > 0)
                                {

                                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                    BindData();
                                    //GridView2.DataBind();
                                    GridView2.SelectedIndex = -1;
                                    update_schedule_emp();
                                    //List<emp_schedule> es = context.emp_schedule.Where("it.[emp_cls_mast_id]='" + empClsMastId.Text.ToString() + "' ").ToList();
                                    //if (es.Count() > 1)
                                    //{
                                    //    int b = es.Count;
                                        
                                    //    foreach ( emp_schedule i in es) 
                                    //    {
                                    //        //ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert10", "alert(" + i + ")", true);
                                    //        i.emp_dateschedule = Convert.ToDateTime(startDate.Text);
                                    //        context.emp_schedule.AddObject(i);
                                    //    }
                                    //    if (context.SaveChanges() > 0)
                                    //    {
                                    //        ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert8", "alert('บันทึกเรียบร้อยแล้ว')", true);
                                    //        //BindData();
                                    //        //GridView4.DataBind();
                                    //        //GridView4.SelectedIndex = -1;
                                    //        //empCode.Text = "";
                                    //        //empName.Text = "";


                                    //    }


                                    //}
                                    //CMD_INSERT.Enabled = true;
                                    //CMD_EDIT.Enabled = false;
                                    empCode.Text = "";
                                    empName.Text = "";
                                    empClsDay.Text = "";
                                    empId.Text = "";
                                    empClsEnd.Text = "";
                                    empClsStart.Text = "";
                                    empClsTotalHours.Text = "";
                                    clsRegDetailId.Text = "";
                                    clsRegCode.Text = "";
                                    clsName.Text = "";
                                    empClsHours.Text = "";
                                    clsCourseTime.Text = "";
                                   
                                    myDDL1.Items.Clear();
                                    CMD_INSERT.Enabled = true;
                                    CMD_EDIT.Enabled = false;
                                    //this.myDDL1.Items.Add("");
                                    //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));
                                   
                                }
                            }
                        }
                        break;
                }
            }
        }
        #endregion

        #region "clear value"
        private void clear()
        {
            empCode.Text = "";
            empName.Text = "";
            empClsDay.Text = "";
            empId.Text = "";
            empClsEnd.Text = "";
            empClsStart.Text = "";
            empClsTotalHours.Text = "";
            clsRegDetailId.Text = "";
            clsRegCode.Text = "";
            clsName.Text = "";
            empClsMastId.Text = "";
            empClsHours.Text = "";
            clsCourseTime.Text = "";
            //empClsSal.Text = "";
            //empClsSalDay.Text = "";
            //empClsSalMonth.Text = "";
            //empClsSalHours.Text = "";
            //this.myDDL1.DataTextField = "";
            //this.myDDL1.DataValueField = "";
            //this.myDDL1.SelectedIndex = 0;
            //this.myDDL1.Items.Add("");
            //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));
       
            
            // เครียร์ค่าใน dropdownlist
            myDDL1.Items.Clear();
            CMD_INSERT.Enabled = true;
            CMD_EDIT.Enabled = false;

            
        }

        protected void CMD_CLEAR_Click(object sender, EventArgs e)
        {
            clear();
        }

        #endregion

        #region "BindData"
        void BindData()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            //CMD_INSERT.Enabled = false;
            //CMD_EDIT.Enabled = true;
            strSQL = "SELECT ecm.emp_id, " +
                     "ecm.emp_cls_day, " +
                     "ecm.emp_cls_start, " +
                     "ecm.emp_cls_end, " +
                     "ecm.emp_cls_total_hours, " +
                     "ecm.emp_cls_hours, " +
                     "ecm.emp_cls_mast_id, " +
                     "em.emp_name, " +
                     "em.emp_code, " +
                     "crd.cls_reg_detail_id, " +
                     "crd.cls_reg_id, " +
                     "crd.cls_course_no, " +
                     "crd.cls_course_time, " +
                     "crd.cls_course_amt, " +
                     "crm.cls_reg_id, " +
                     "crm.cls_reg_code, " +
                     "crm.level, " +
                     "crm.cls_name, " +
                     "concat(crm.cls_name,'(',crd.cls_course_time,')') as name_course "+
                "FROM emp_cls_mast ecm " +
                "LEFT OUTER JOIN emp_mast em " +
                "ON ecm.emp_id = em.emp_id " +
                "LEFT OUTER JOIN cls_register_detail crd " +
                "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
                "LEFT OUTER JOIN cls_register_mast crm " +
                "ON crd.cls_reg_id = crm.cls_reg_id ";



            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();

            //*** BindData to GridView ***//
            GridView2.DataSource = dtReader;
            GridView2.DataBind();

            

        }
        #endregion

        #region "GridView2_SelectedIndexChanging"
        protected void GridView2_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {

            CMD_INSERT.Enabled = false;
            CMD_EDIT.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;

            


            strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, "+
                     "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, ecm.start_date, " +
                     "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name , ecm.pay_type, ecm.emp_cls_sal,ecm.emp_cls_sal_month,ecm.emp_cls_sal_day,ecm.emp_cls_sal_hours " +
                     "FROM emp_cls_mast ecm "+
                     "LEFT OUTER JOIN emp_mast em "+
                        "ON ecm.emp_id = em.emp_id "+
                     "LEFT OUTER JOIN cls_register_detail crd "+
                        "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id "+
                     "LEFT OUTER JOIN cls_register_mast crm "+
                        "ON crd.cls_reg_id = crm.cls_reg_id " +
                     "where ecm.emp_cls_mast_id = '" + GridView2.DataKeys[e.NewSelectedIndex].Value + "'";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                empId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["emp_id"]).ToString();
                empCode.Text = (string)ds4.Tables[0].Rows[i]["emp_code"].ToString();
                empName.Text = (string)ds4.Tables[0].Rows[i]["emp_name"].ToString();
                empClsDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_day"].ToString();
                empClsStart.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_start"].ToString();
                empClsEnd.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_end"].ToString();
                empClsTotalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_total_hours"].ToString();
                clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                clsRegDetailId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                empClsMastId.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_mast_id"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                empClsHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_hours"].ToString();
                startDate.Text = string.Format("{0:dd/MM/yyyy}", ds4.Tables[0].Rows[i]["start_date"]).ToString();
                    
                //empClsSal.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal"].ToString();
                //empClsSalMonth.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString()));
                //empClsSalMonth.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_month"].ToString();
                //empClsSalDay.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString()));
                //empClsSalDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_day"].ToString();
                //empClsSalHours.Text = String.Format("{0:0.00}", Convert.ToDouble((string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString()));
                //empClsSalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_sal_hours"].ToString(); 
                //radio.SelectedValue = (string)ds4.Tables[0].Rows[i]["pay_type"].ToString();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[0]["cls_reg_detail_id"].ToString();
                //this.radio.DataSource = ds4;
                //this.radio.DataTextField = "SexDesc";
                //this.radio.DataValueField = "pay_type";
                //this.radio.DataBind();
                
                string strSQL2;
                strSQL2 = "SELECT * FROM  cls_register_detail crd  " +
                     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                     "where crm.cls_reg_code = '" + clsRegCode.Text + "  '";



                MySqlConnection Conn1 = new MySqlConnection(StrConn);
                Conn1.Open();
                MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn1);
                DataSet ds2 = new DataSet();
                
                da2.Fill(ds2);
                //*** DropDownList ***//
                this.myDDL1.DataSource = ds2;
                this.myDDL1.DataTextField = "cls_course_no";
                this.myDDL1.DataValueField = "cls_reg_detail_id";
                this.myDDL1.DataBind();
                this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                da2 = null;
                Conn1.Close();
                Conn1 = null;
                //dtAdapter = null;
                //objConn.Close();
                //objConn = null;
               

                //int _row2 = ds2.Tables[0].Rows.Count;
                //int j = 0;
                //if (_row2 > j)
                //{
                    
                    
                    //this.myDDL1.SelectedValue = "cls_reg_detail_id";
                    //this.myDDL1.SelectedItem = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                    
                    //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                    //this.myDDL1.DataBind();	
                    //this.myDDL1.DataSource = ds4;
                    //this.myDDL1.DataTextField = (string)ds2.Tables[0].Rows[i]["cls_course_no"].ToString();
                    //this.myDDL1.DataValueField = (string)ds2.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                    
                    //this.myDDL1.DataTextField = "cls_course_no";
                    //this.myDDL1.DataValueField = "cls_reg_detail_id";
                     
                //}
                
            }
            
            
        }

        #endregion

        #region "delete value emp_cls_mast"
        protected void GridView4_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "DELETE FROM emp_cls_mast WHERE emp_cls_mast_id ='" + GridView2.DataKeys[e.RowIndex].Value + "'";
            Conn.Open();
            objCmd = new MySqlCommand(strSQL, Conn);

            objCmd.ExecuteNonQuery();


            MySqlConnection Conn1 = new MySqlConnection(StrConn);
            string strSQL1;
            strSQL1 = "DELETE FROM emp_schedule WHERE emp_cls_mast_id ='" + GridView2.DataKeys[e.RowIndex].Value + "'";
            Conn1.Open();
            objCmd = new MySqlCommand(strSQL1, Conn1);

            objCmd.ExecuteNonQuery();

            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert3", "alert('ลบข้อมูลเรียบร้อยแล้ว')", true);
            GridView2.EditIndex = -1;

            BindData();

        }
        #endregion

        #region "Confrim delete value"
        protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                // loop all data rows
                foreach (DataControlFieldCell cell in e.Row.Cells)
                {
                    // check all cells in one row
                    foreach (Control control in cell.Controls)
                    {
                        // Must use LinkButton here instead of ImageButton
                        // if you are having Links (not images) as the command button.
                        ImageButton button = control as ImageButton;
                        if (button != null && button.CommandName == "Delete")
                            // Add delete confirmation
                            button.OnClientClick = "if (!confirm('คุณต้องการที่จะลบข้อมูล?')) return;";
                    }
                }
            }
        }
        #endregion

        //#region
        //protected void CMD_SCHEDULE_Click(object sender, EventArgs e)
        //{

        //    using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
        //    {
        //        Button bt = (Button)sender;
        //        switch (bt.ID)
        //        {
        //            case "CMD_SCHEDULE":

                        
                        
        //                int i = 0;
        //                string a = "";
        //                for (i = 0; i <= Convert.ToInt32(clsCourseTime.Text) - 1; i++)
        //                {
        //                    emp_schedule ecm = new emp_schedule();
                            
        //                    if(i==0)
        //                    {
        //                        ecm.emp_dateschedule = Convert.ToDateTime(startDate.Text);
        //                        a = startDate.Text; 
        //                    }else{
        //                        a = Convert.ToDateTime(a).AddDays(7).ToString();
        //                    ecm.emp_dateschedule = Convert.ToDateTime(a).AddDays(7);
                            
        //                    }
        //                    ecm.emp_cls_mast_id = empClsMastId.Text;
                            
        //                    ecm.emp_id = empId.Text;

        //                    context.emp_schedule.AddObject(ecm);
        //                }
                        
                        
        //                // context.SaveChanges();
        //                if (context.SaveChanges() > 0)
        //                {
        //                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('บันทึกเรียบร้อยแล้ว')", true);
        //                    //BindData();
        //                    //GridView4.DataBind();
        //                    //GridView4.SelectedIndex = -1;
        //                    //empCode.Text = "";
        //                    //empName.Text = "";
                            

        //                }

        //                break;
        //            case "CMD_EDss":

        //                List<emp_cls_mast> crm2 = context.emp_cls_mast.Where("it.[emp_cls_mast_id]=" + empClsMastId.Text + " ").ToList();
        //                if (crm2.Count() == 1)
        //                {
        //                    emp_cls_mast crmEdit = context.emp_cls_mast.Where("it.[emp_cls_mast_id]=" + empClsMastId.Text + " ").First();
        //                    if (crmEdit != null)
        //                    {

        //                        crmEdit.emp_id = Convert.ToInt32(empId.Text);
        //                        crmEdit.emp_cls_day = empClsDay.Text;
        //                        crmEdit.emp_cls_total_hours = empClsTotalHours.Text;
        //                        crmEdit.emp_cls_start = empClsStart.Text;
        //                        crmEdit.emp_cls_end = empClsEnd.Text;
        //                        crmEdit.emp_cls_hours = empClsHours.Text;
        //                        //crmEdit.pay_type = radio.SelectedValue;
        //                        //ecm.cls_reg_detail_id = Convert.ToInt32(clsRegDetailId.Text);
        //                        crmEdit.cls_reg_detail_id = Convert.ToInt32(myDDL1.SelectedValue);
        //                        crmEdit.update_by = "Tong";
        //                        crmEdit.update_date = DateTime.Now;
                              

        //                        if (context.SaveChanges() > 0)
        //                        {

        //                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
        //                            BindData();
        //                            //GridView2.DataBind();
        //                            GridView2.SelectedIndex = -1;
        //                            //CMD_INSERT.Enabled = true;
        //                            //CMD_EDIT.Enabled = false;
        //                            empCode.Text = "";
        //                            empName.Text = "";
        //                            empClsDay.Text = "";
        //                            empId.Text = "";
        //                            empClsEnd.Text = "";
        //                            empClsStart.Text = "";
        //                            empClsTotalHours.Text = "";
        //                            clsRegDetailId.Text = "";
        //                            clsRegCode.Text = "";
        //                            clsName.Text = "";
        //                            empClsHours.Text = "";
        //                            clsCourseTime.Text = "";
        //                            //empClsSal.Text = "";
        //                            //empClsSalDay.Text = "";
        //                            //empClsSalMonth.Text = "";
        //                            //empClsSalHours.Text = "";
        //                            myDDL1.Items.Clear();
        //                            CMD_INSERT.Enabled = true;
        //                            CMD_EDIT.Enabled = false;
        //                            //this.myDDL1.Items.Add("");
        //                            //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));

        //                        }
        //                    }
        //                }
        //                break;
        //        }
        //    }
        //    //MySqlConnection Conn1 = new MySqlConnection(StrConn);
        //    //string strSQL1;
        //    //Conn1.Open();
        //    //int i = 0;

        //    //for (i = 0; i <= Convert.ToInt32(clsCourseTime.Text) - 1; i++)
        //    //{
                
        //    //    DateTime d1 ;
        //    //    if(i==0)
        //    //    {
        //    //    d1 = Convert.ToDateTime(TextBox1.Text);
        //    //    }else{
        //    //    d1 = Convert.ToDateTime(TextBox1.Text).AddDays(7);
        //    //    }
        //    //    strSQL1 = "INSERT INTO emp_schedule (emp_dateschedule , emp_cls_mast_id, emp_id) " + 
        //    //              " VALUES (" + d1.ToShortDateString() + ",'" +empClsMastId.Text+ "','"+empId.Text+"' )";





        //    //    //MySqlDataAdapter da5 = new MySqlDataAdapter(strSQL1, Conn1);
                
        //    //    objCmd.Connection = Conn1;
        //    //    objCmd.CommandType = CommandType.Text;
        //    //    objCmd.CommandText = strSQL1;

        //    //    objCmd.ExecuteNonQuery();
            
            
        //    //}

        //}
        //#endregion


        #region "calulate value sal_month,day,hours"
        //protected void empClsSal_TextChanged1(object sender, EventArgs e)
        //{
           
        //    if(!empClsSal.Text.Equals(""))
        //    {
        //        //ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert3", "alert('OK')", true);
        //        if (radio.SelectedValue == "0")
        //        {
        //            double salaryPerMonth = (Convert.ToDouble(empClsSal.Text));
        //            empClsSalMonth.Text = salaryPerMonth.ToString();
        //            empClsSalDay.Text = "";
        //            empClsSalHours.Text = "";
        //        }
        //        else if (radio.SelectedValue == "1")
        //        {
        //            double salaryPerDay = (Convert.ToDouble(empClsSal.Text)/4);
        //            empClsSalDay.Text = salaryPerDay.ToString();
        //            empClsSalMonth.Text = "";
        //            empClsSalHours.Text = "";
        //        }
        //        else if (radio.SelectedValue == "2")
        //        {
        //            if (!empClsTotalHours.Text.Equals("") && !empClsHours.Text.Equals(""))
        //            {
        //                double salaryPerHours = ((Convert.ToDouble(empClsSal.Text) / Convert.ToDouble(empClsTotalHours.Text)) * Convert.ToDouble(empClsHours.Text));
        //                empClsSalHours.Text = salaryPerHours.ToString();
        //                empClsSalDay.Text = "";
        //                empClsSalMonth.Text = "";
        //            }
        //            else { }
        //        }
        //        else {
        //            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert4", "alert('กรุณาระบุวิธีการจ่ายเงิน')", true);
        //        }
        //    }
            
        //}
        #endregion

        //#region "calulate value sal_month,day,hours"
        //protected void empClsSal_TextChanged2(object sender, EventArgs e)
        //{

        //    if (!empClsSal.Text.Equals(""))
        //    {
                
                
        //            double salaryPerMonth = (Convert.ToDouble(empClsSal.Text)* 4);
        //            empClsSalMonth.Text = String.Format("{0:0.00}", salaryPerMonth); 
        //            //empClsSalMonth.Text = salaryPerMonth.ToString();
                    
                
        //            double salaryPerDay = (Convert.ToDouble(empClsSal.Text));
        //            empClsSalDay.Text = String.Format("{0:0.00}", salaryPerDay); 
        //            //empClsSalDay.Text = salaryPerDay.ToString();
                    
               
        //            if (!empClsTotalHours.Text.Equals("") && !empClsHours.Text.Equals(""))
        //            {
        //              //  double salaryPerHours = ((Convert.ToDouble(empClsSal.Text) / Convert.ToDouble(empClsTotalHours.Text)) * Convert.ToDouble(empClsHours.Text));
        //                double salaryPerHours = (Convert.ToDouble(empClsSal.Text) / Convert.ToDouble(empClsHours.Text));
        //                empClsSalHours.Text = String.Format("{0:0.00}", salaryPerHours); 
        //                //empClsSalHours.Text = salaryPerHours.ToString();
                        
        //            }
                    
                
                
        //    }

        //}
        //#endregion

        //#region "clear value when radiobuttom changed value "
        //protected void radio_SelectedIndexChanged(object sender, EventArgs e)
        //{
        //    empClsSal.Text = "";
        //    empClsSalDay.Text = "";
        //    empClsSalHours.Text = "";
        //    empClsSalMonth.Text = "";

        //}
        //#endregion
        

        void update_schedule_emp()
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                List<emp_schedule> es = context.emp_schedule.Where("it.[emp_cls_mast_id]='" + empClsMastId.Text.ToString() + "' ").ToList();
                if (es.Count() > 1)
                {
                    int b = es.Count;

                    //foreach (emp_schedule i in es)
                    //{
                    //    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert10", "alert(" + i + ")", true);
                    //    emp_schedule es1 = context.emp_schedule.Where("it.[schedule_emp_id]=" + i.schedule_emp_id + " ").First();
                    //    if (es1 != null)
                    //    {
                            
                    //        es1.emp_dateschedule = Convert.ToDateTime(startDate.Text);
                    //        context.emp_schedule.AddObject(es1);
                    //    }
                    //    if (context.SaveChanges() > 0)
                    //    {
                    //        ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert8", "alert('บันทึกเรียบร้อยแล้ว')", true);
                    //        //BindData();
                    //        //GridView4.DataBind();
                    //        //GridView4.SelectedIndex = -1;
                    //        //empCode.Text = "";
                    //        //empName.Text = "";


                    //    }
                    //}
                    MySqlConnection Conn1 = new MySqlConnection(StrConn);
                    string strSQL1;
                    Conn1.Open();
                    int j = 0;
                    string a = "";
                    foreach (emp_schedule i in es)
                    {
                        
                        if (j == 0)
                        {
                            //es.emp_dateschedule = Convert.ToDateTime(startDate.Text);
                            a = startDate.Text;
                        }
                        else
                        {
                            a = Convert.ToDateTime(a).AddDays(7).ToShortDateString();
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert15", "alert(" + Convert.ToDateTime(a).AddDays(7).ToString() + ")", true);
                           // es.emp_dateschedule = Convert.ToDateTime(a).AddDays(7);

                        }
                        j++;
                        strSQL1 = " update emp_schedule SET " +
                      " emp_dateschedule = '" + toDate(a) + "' " +
                      " ,create_by = 'Tong' " +
                      " ,create_date = '"+toDate(DateTime.Now.ToShortDateString())+"' " + 
                      " WHERE schedule_emp_id = '" + i.schedule_emp_id + "' "; 


                        objCmd.Connection = Conn1;
                        objCmd.CommandType = CommandType.Text;
                        objCmd.CommandText = strSQL1;

                        objCmd.ExecuteNonQuery();


                    }
                    


                }

            }

        }
        public static String toDate(String date)
        {
            String day = date.Split('/')[0];
            String month = date.Split('/')[1];
            String year = (Convert.ToInt32(date.Split('/')[2]) - 543).ToString();
            //String year = date.Split('/')[2];

            //DateTime dateForMat = DateTime.Parse((day + "/" + month + "/" + year));
            //String dateUs = string.Format("{0:yyyy/MM/dd HH:mm:ss}", dateForMat);
            //return dateUs;
            return string.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.Parse((day + "/" + month + "/" + year)));
        }

    }
}